/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.db.tables;

import com.google.api.adwords.starterkit.db.keys.KeywordKey;
import com.google.api.adwords.starterkit.db.structures.KeywordStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;
import com.google.api.adwords.v201101.cm.KeywordMatchType;
import com.google.api.adwords.v201101.cm.UserStatus;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;

/**
 * The Class KeywordTable encapsulates most of the access to Keyword DB.
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class KeywordTable {  
  private PreparedStatement prepNew;
  private PreparedStatement prepUpdate;
  private PreparedStatement prepLoad;
  private PreparedStatement prepTouched;
  private PreparedStatement prepUpdated;
  private PreparedStatement prepBatchUpdated;
  private PreparedStatement prepExists;
  private PreparedStatement prepLocalId;
  private PreparedStatement prepKeywordId;
  private Connection conn;
  private PreparedStatement prepResetIsUpdated;
  private PreparedStatement prepResetIsTouched;
  private PreparedStatement prepStatsUpdate;
  private static final Logger log = Logger.getLogger("StarterKit");
  
  /**
   * Instantiates a new keyword utils.
   *
   * @param conn the conn
   */
  public KeywordTable(Connection conn) {
    this.conn = conn;
    try {
      Statement stat = conn.createStatement();
      stat.executeUpdate(
          "CREATE TABLE IF NOT EXISTS Keywords "
        + "  ( "
        + "     LocalId              INTEGER PRIMARY KEY, "
        + "     KeywordId            LONG DEFAULT 0, "
        + "     LocalAdGroupId       LONG DEFAULT 0, "
        + "     AdGroupId            LONG DEFAULT 0, "
        + "     DestinationUrl       STRING DEFAULT '', "
        + "     MaxCpc               LONG DEFAULT 0, "
        + "     MaxCpm               LONG DEFAULT 0, "
        + "     MatchType            STRING DEFAULT '', "
        + "     isNegative           BOOLEAN DEFAULT 0, "
        + "     Status               STRING DEFAULT '', "
        + "     Text                 STRING DEFAULT '', "
        + "     Ctr                  FLOAT DEFAULT 0, "
        + "     Clicks               INTEGER DEFAULT 0, "
        + "     Cost                 FLOAT DEFAULT 0, "
        + "     Impressions          INTEGER DEFAULT 0, "
        + "     Conversions          INTEGER DEFAULT 0, "
        + "     Transactions         INTEGER DEFAULT 0, "
        + "     IsStatusModifiable   BOOLEAN DEFAULT 1, "
        + "     IsMatchModifiable    BOOLEAN DEFAULT 1, "
        + "     IsBidModifiable      BOOLEAN DEFAULT 1, "
        + "     IsUrlModifiable      BOOLEAN DEFAULT 1, "
        + "     IsTouched            BOOLEAN DEFAULT 0, "
        + "     IsUpdated            BOOLEAN DEFAULT 0, "
        + "     IsStatsAvailable     BOOLEAN DEFAULT 0, "
        + "     IsBanned             BOOLEAN DEFAULT 0, "
        + "     LastUpdate           INTEGER DEFAULT 0, "
        + "     AveragePosition      FLOAT DEFAULT 0, "
        + "     AdGroupMaxCpa        FLOAT DEFAULT 0, "
        + "     CostPerConverstion   FLOAT DEFAULT 0, "
        + "     CostPerTransaction   FLOAT DEFAULT 0, "
        + "     FirstPageCpc         FLOAT DEFAULT 0, "
        + "     ApiErrorMsg          STRING DEFAULT '', "
        + "     ApiErrorTrigger      STRING DEFAULT ''"
        + "  );");          
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS KeywordsLocalAdGroupIdTextMatchTypeIndex " 
        + "on Keywords (LocalAdGroupId, Text, MatchType);");
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS KeywordsAdGroupIdKeywordIdIndex " 
        + "on Keywords (AdGroupId, KeywordId);");
      
      prepLocalId = conn.prepareStatement(
          "SELECT LocalId FROM Keywords WHERE KeywordId = ?;");
      prepExists = conn.prepareStatement(
          "SELECT Text FROM Keywords where Text = ? AND LocalAdGroupId = ? " 
        + "AND MatchType = ?;");
      prepLoad = conn.prepareStatement(
          "SELECT * FROM Keywords WHERE Text = ? AND LocalAdGroupId = ? " 
        + "AND MatchType = ?;");
      
      prepTouched = conn.prepareStatement(
          "UPDATE Keywords SET IsTouched = ? WHERE Text = ? " 
        + "AND LocalAdGroupId = ? AND MatchType = ?;");
      
      prepUpdated = conn.prepareStatement(
          "UPDATE Keywords SET IsUpdated = ? WHERE Text = ? " 
        + "AND LocalAdGroupId = ? AND MatchType = ?;");
      
      prepBatchUpdated = conn.prepareStatement(
          "UPDATE Keywords SET IsUpdated = ?, IsTouched = ?, ApiErrorMsg = ?, " 
        + "ApiErrorTrigger = ? WHERE Text = ? AND " 
        + "LocalAdGroupId = ? AND MatchType = ?;");
      
      prepKeywordId = conn.prepareStatement(
          "UPDATE Keywords SET KeywordId = ?, IsUpdated = ?, IsTouched = ? " +
          "WHERE Text = ? AND LocalAdGroupId = ? AND MatchType = ?;");

      prepNew = conn.prepareStatement(
          "INSERT INTO Keywords (LastUpdate, KeywordId, LocalAdGroupId, " 
        + "AdGroupId, DestinationUrl, MaxCpc, MatchType, isNegative, Status, " 
        + "Text, Ctr, Clicks, Cost, Impressions, Conversions, Transactions, " 
        + "IsStatusModifiable, IsMatchModifiable, IsBidModifiable, " 
        + "IsUrlModifiable, " 
        + "IsUpdated, IsStatsAvailable, IsBanned, IsTouched, MaxCpm) " 
        + "VALUES (?, ?, ?, ?, " 
        + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
      
      prepStatsUpdate = conn.prepareStatement(
            "UPDATE Keywords SET Ctr = ?, Clicks = ?, Cost = ?, "
          + "Impressions = ?, Conversions = ?, Transactions = ? , "
          + "AveragePosition = ?, AdGroupMaxCpa = ?, "
          + "CostPerConverstion = ?, CostPerTransaction = ?, "
          + "FirstPageCpc = ?, IsStatsAvailable = 1 WHERE KeywordId = ? "
          + "AND AdGroupId = ?;");
      
      prepUpdate = conn.prepareStatement(
          "REPLACE INTO Keywords (LocalId, KeywordId, LocalAdGroupId, " 
        + "AdGroupId, DestinationUrl, MaxCpc, MatchType, isNegative, Status, " 
        + "Text, Ctr, Clicks, Cost, Impressions, Conversions, Transactions, " 
        + "IsStatusModifiable, IsMatchModifiable, IsBidModifiable, " 
        + "IsUrlModifiable, IsUpdated, IsStatsAvailable, IsBanned, IsTouched, " 
        + "MaxCpm) VALUES (?, ?, ?, ?, ?, "
        + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");     
      prepResetIsUpdated = conn.prepareStatement(
          "UPDATE Keywords SET IsUpdated = ?");
      prepResetIsTouched = conn.prepareStatement(
          "UPDATE Keywords SET IsTouched = ?");
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }

  /**
   * Verifies if a keyword exists on DB.
   *
   * @param keywordKey the keyword primary key
   * @return true, if exists
   */
  public boolean exists(KeywordKey keywordKey) {
    boolean hasKey = false;
    ResultSet rs = null;
    try {
      prepExists.setString(1, keywordKey.getText());      
      prepExists.setInt(2, keywordKey.getLocalAdGroupId());
      prepExists.setString(3, 
          keywordKey.getMatchType().getValue());
      rs = prepExists.executeQuery();
      if (rs.next()) {
        hasKey = true;
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return hasKey;
  }

  /**
   * Gets the keyword structure from a generic SQL query result 
   *
   * @param rs the ResultSet representing the SQL query result
   * @return the KeywordStructure
   */
  public KeywordStructure getStructure(ResultSet rs) {
    KeywordStructure kwdStructure = new KeywordStructure();
    try {
      kwdStructure.setLocalId(rs.getInt("LocalId"));
      kwdStructure.setKeywordId(rs.getLong("KeywordId"));
      kwdStructure.setLocalAdGroupId(rs.getInt("LocalAdGroupId"));
      kwdStructure.setAdGroupId(rs.getLong("AdGroupId"));
      kwdStructure.setDestinationUrl(rs.getString("DestinationUrl"));
      kwdStructure.setMaxCpc(rs.getLong("MaxCpc"));
      kwdStructure.setMaxCpm(rs.getLong("MaxCpm"));
      kwdStructure.setMatchType(rs.getString("MatchType"));
      kwdStructure.setNegative(rs.getBoolean("isNegative"));
      kwdStructure.setStatus(UserStatus.fromString(rs.getString("Status")));
      kwdStructure.setText(rs.getString("Text"));
      kwdStructure.setCtr(rs.getFloat("Ctr"));
      kwdStructure.setClicks(rs.getLong("Clicks"));
      kwdStructure.setCost(rs.getFloat("Cost"));
      kwdStructure.setImpressions(rs.getLong("Impressions"));
      kwdStructure.setConversions(rs.getLong("Conversions"));
      kwdStructure.setTransactions(rs.getLong("Transactions"));

      kwdStructure.setMatchModifiable(rs.getBoolean("IsMatchModifiable"));
      kwdStructure.setStatusModifiable(rs.getBoolean("IsStatusModifiable"));
      kwdStructure.setCpcModifiable(rs.getBoolean("IsBidModifiable"));
      kwdStructure.setUrlModifiable(rs.getBoolean("IsUrlModifiable"));
      kwdStructure.setBanned(rs.getBoolean("IsBanned"));

      kwdStructure.setHasStats(rs.getBoolean("IsStatsAvailable"));
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return kwdStructure;
  }

  /**
   * Load a keyword from DB.
   *
   * @param keywordKey the keyword key primary key
   * @return the keyword structure
   */
  public KeywordStructure get(KeywordKey keywordKey) {
    KeywordStructure kwdStructure = null;
    ResultSet rs = null;
    try {
      prepLoad.setString(1, keywordKey.getText());
      prepLoad.setInt(2, keywordKey.getLocalAdGroupId());
      prepLoad.setString(3, 
          keywordKey.getMatchType().getValue());
      rs = prepLoad.executeQuery();
      if (rs.next()) {
        kwdStructure = getStructure(rs);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return kwdStructure;
  }

  /**
   * Execute all SQLs on the batch pipelines
   */
  public void executeBatch() {
    try {
      prepNew.executeBatch();
      prepUpdate.executeBatch();
      prepKeywordId.executeBatch();
      prepBatchUpdated.executeBatch();   
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }
    
  /**
   * Adds an keyword update to the to batch pipeline
   *
   * @param kwdStructure the kwd structure
   * @param updated the updated flag
   * @param touched the touched flag
   */
  public void addToBatch(KeywordStructure kwdStructure, boolean updated, 
      boolean touched) {
    int count = 1;
    PreparedStatement prep = null;
    try {
      if (kwdStructure.getLocalId() == 0) {
        prep = prepNew;
        prep.setLong(count++, new Date().getTime());
      } else {
        prep = prepUpdate;
        prep.setLong(count++, kwdStructure.getLocalId());
      }
      prep.setLong(count++, kwdStructure.getKeywordId());
      prep.setInt(count++, kwdStructure.getLocalAdGroupId());
      prep.setLong(count++, kwdStructure.getAdGroupId());            
      prep.setString(count++, kwdStructure.getDestinationUrl());
      prep.setLong(count++, kwdStructure.getMaxCpc());
      prep.setString(count++, 
          kwdStructure.getMatchType().getValue());
      prep.setBoolean(count++, kwdStructure.isNegative());
      prep.setString(count++, kwdStructure.getStatus().getValue());
      prep.setString(count++, kwdStructure.getText());
      prep.setFloat(count++, kwdStructure.getCtr());
      prep.setLong(count++, kwdStructure.getClicks());
      prep.setFloat(count++, kwdStructure.getCost());
      prep.setLong(count++, kwdStructure.getImpressions());
      prep.setLong(count++, kwdStructure.getConversions());
      prep.setLong(count++, kwdStructure.getTransactions());
      prep.setBoolean(count++, kwdStructure.isStatusModifiable());
      prep.setBoolean(count++, kwdStructure.isMatchModifiable());
      prep.setBoolean(count++, kwdStructure.isCpcModifiable());
      prep.setBoolean(count++, kwdStructure.isUrlModifiable());
      prep.setBoolean(count++, updated);
      prep.setBoolean(count++, kwdStructure.isStatsAvailable());
      prep.setBoolean(count++, kwdStructure.isBanned());
      prep.setBoolean(count++, touched);
      prep.setLong(count++, kwdStructure.getMaxCpm());
      prep.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Save a keyword into the DB
   *
   * @param kwdStructure the kwd structure
   * @param updated the updated flag
   * @param touched the touched flag
   * @return the keyword structure with localid
   */
  public KeywordStructure save(KeywordStructure kwdStructure, boolean updated, 
      boolean touched) {
    int count = 1;
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      if (kwdStructure.getLocalId() == 0) {
        prep = prepNew;
        prep.setLong(count++, new Date().getTime());
      } else {
        prep = prepUpdate;
        prep.setLong(count++, kwdStructure.getLocalId());
      }
      prep.setLong(count++, kwdStructure.getKeywordId());
      prep.setInt(count++, kwdStructure.getLocalAdGroupId());
      prep.setLong(count++, kwdStructure.getAdGroupId());            
      prep.setString(count++, kwdStructure.getDestinationUrl());
      prep.setLong(count++, kwdStructure.getMaxCpc());
      prep.setString(count++, 
          kwdStructure.getMatchType().getValue());
      prep.setBoolean(count++, kwdStructure.isNegative());
      prep.setString(count++, kwdStructure.getStatus().getValue());
      prep.setString(count++, kwdStructure.getText());
      prep.setFloat(count++, kwdStructure.getCtr());
      prep.setLong(count++, kwdStructure.getClicks());
      prep.setFloat(count++, kwdStructure.getCost());
      prep.setLong(count++, kwdStructure.getImpressions());
      prep.setLong(count++, kwdStructure.getConversions());
      prep.setLong(count++, kwdStructure.getTransactions());
      prep.setBoolean(count++, kwdStructure.isStatusModifiable());
      prep.setBoolean(count++, kwdStructure.isMatchModifiable());
      prep.setBoolean(count++, kwdStructure.isCpcModifiable());
      prep.setBoolean(count++, kwdStructure.isUrlModifiable());
      prep.setBoolean(count++, updated);
      prep.setBoolean(count++, kwdStructure.isStatsAvailable());
      prep.setBoolean(count++, kwdStructure.isBanned());
      prep.setBoolean(count++, touched);
      prep.setLong(count++, kwdStructure.getMaxCpm());
      prep.executeUpdate();

      rs = prep.getGeneratedKeys();      
      kwdStructure.setLocalId(rs.getInt(1));
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return kwdStructure;
  }

  /**
   * Sets the touched flag
   *
   * @param keywordKey the keyword primary key
   * @param touched the touched flag
   */
  public void setTouched(KeywordKey keywordKey, boolean touched) {
    try {
      prepTouched.setBoolean(1, touched);
      prepTouched.setString(2, keywordKey.getText());
      prepTouched.setInt(3, keywordKey.getLocalAdGroupId());
      prepTouched.setString(4, 
          keywordKey.getMatchType().getValue());
      prepTouched.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Sets the updated flag.
   *
   * @param keywordKey the keyword primary key 
   * @param updated the updated flag
   */
  public void setUpdated(KeywordKey keywordKey, boolean updated) {
    try {
      prepUpdated.setBoolean(1, updated);
      prepUpdated.setString(2, keywordKey.getText());
      prepUpdated.setInt(3, keywordKey.getLocalAdGroupId());
      prepUpdated.setString(4, 
          keywordKey.getMatchType().getValue());
      prepUpdated.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  /**
   * Add a updated and touched flags to a batch process 
   *
   * @param keywordKey the keyword primary key
   * @param updated the updated flag
   * @param touched the touched flag
   */
  public void addBatchUpdated(KeywordKey keywordKey, boolean updated, 
      boolean touched, String error, String trigger) {
    try {
      prepBatchUpdated.setBoolean(1, updated);
      prepBatchUpdated.setBoolean(2, touched);
      prepBatchUpdated.setString(3, error);
      prepBatchUpdated.setString(4, trigger);
      prepBatchUpdated.setString(5, keywordKey.getText());
      prepBatchUpdated.setInt(6, keywordKey.getLocalAdGroupId());
      prepBatchUpdated.setString(7, 
          keywordKey.getMatchType().getValue());
      prepBatchUpdated.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Add a keyword id to a batch update
   *
   * @param keywordKey the keyword primary key
   * @param keywordId the keyword id
   * @param updated the updated flag
   * @param touched the touched flag
   */
  public void addBatchKeywordId(KeywordKey keywordKey, long keywordId, 
      boolean updated, boolean touched) {
    try {
      prepKeywordId.setLong(1, keywordId);
      prepKeywordId.setBoolean(2, updated);
      prepKeywordId.setBoolean(3, touched);
      prepKeywordId.setString(4, keywordKey.getText());
      prepKeywordId.setInt(5, keywordKey.getLocalAdGroupId());
      prepKeywordId.setString(6, 
          keywordKey.getMatchType().getValue());
      prepKeywordId.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }

  /**
   * Gets the keyword local id form DB
   *
   * @param keywordId the keyword id from AdWords
   * @return the local id
   */
  public int getLocalId(long keywordId) {
    int localId = 0;
    ResultSet rs = null;
    try {
      prepLocalId.setLong(1, keywordId);
      rs = prepLocalId.executeQuery();
      if (rs.next()) {
        localId = rs.getInt("LocalId"); 
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }    
    return localId;
  }

  /**
   * Reset all keywords flags to its default values 
   */
  public void reset() {
    PreparedStatement prep;
    try {
      prep = conn.prepareStatement(
          "UPDATE Keywords SET IsUpdated = 0, IsTouched = 0, " 
        + "IsStatusModifiable = 1, IsBidModifiable = 1, " 
        + "IsMatchModifiable = 1, IsUrlModifiable = 1;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }        
  }

  /**
   * Update ad groups ids at keyword table.
   */
  public void updateAdGroupsIds() {
    log.debug("Updating AdGroupId on new Keywords");
    HashMap<Integer, Long> adGroupsIdMap = getNewAdGroupsIds();
    PreparedStatement prep;
    try {
      prep = conn.prepareStatement(
            "UPDATE Keywords SET AdGroupId = ? " 
          + "WHERE LocalAdGroupId = ? AND AdGroupId = 0");
      int count = 0;
      for (int localAdGroupId : adGroupsIdMap.keySet()) {
        long adGroupId = adGroupsIdMap.get(localAdGroupId);
        prep.setLong(1, adGroupId);
        prep.setLong(2, localAdGroupId);
        prep.addBatch();
        count++;
        if (count == 100000) {
          count = 0;
          prep.executeBatch();
        }
      }
      prep.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }      
  }

  /**
   * Gets the adgroup ids map.
   *
   * @return the ad group ids map
   */
  public HashMap<Integer, Long> getNewAdGroupsIds() {
    log.debug("Loading AdGroups Ids needed by new Keywords");
    HashMap<Integer, Long> adGroupIdsMap = new HashMap<Integer, Long>(); 
    ResultSet rs = null;
    try {
      Statement stat = conn.createStatement();
      rs = stat.executeQuery(
            "SELECT ag.LocalId, ag.AdGroupId FROM AdGroups ag, Keywords ke "
          + "WHERE ke.LocalAdGroupId = ag.LocalId and ke.AdGroupId = 0;");
      while (rs.next()) {
        adGroupIdsMap.put(rs.getInt("LocalId"), rs.getLong("AdGroupId"));
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return adGroupIdsMap;
  }
  
  /**
   * Return all Keywords modified during the last Starter Kit execution
   * 
   * @return the ResultSet that contains Keywords modified 
   */
  public ResultSet getModified() {
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      prep = conn.prepareStatement(
          "SELECT * FROM Keywords WHERE IsUpdated = 1 AND IsBanned = 0");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return rs;
  }

  /**
   * Resets IsUpdated flag in all rows of AdGroups table
   */
  public void resetIsUpdated(boolean isUpdated) {
    try {
      prepResetIsUpdated.setBoolean(1, isUpdated);
      prepResetIsUpdated.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }

  /**
   * Resets IsUpdated flag in all rows of AdGroups table
   */
  public void resetIsTouched(boolean isTouched) {
    try {
      prepResetIsTouched.setBoolean(1, isTouched);
      prepResetIsTouched.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }

  /**
   * Set the Status to PAUSED for all Keywords not touched by the 
   * application.
   */
  public void autoPause() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE Keywords SET Status = ? WHERE isTouched = 0 " +
          "AND Status NOT IN (?, ?)");
      prep.setString(1, UserStatus.PAUSED.getValue());
      prep.setString(2, UserStatus.PAUSED.getValue());
      prep.setString(3, UserStatus.DELETED.getValue());
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }

  /**
   * Resets IsBanned flag in all rows of Keyword table
   */
  public void resetIsBanned(boolean isBanned) {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE Keywords SET IsBanned = ?");
      prep.setBoolean(1, isBanned);
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }      
  }

  /**
   * Grant that all banned Keywords will not be updated by StarterKit
   * syncronization process.
   */
  public void grantBanned() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE Keywords SET IsUpdated = 0, IsTouched = 0 " +
          "WHERE IsBanned = 1");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }
  
  /**
   * Resets all keywords stats from AdWords
   */
  public void resetKeywordsStats() {
    PreparedStatement prep = null;
    try {
      prep = conn
          .prepareStatement(
                "UPDATE Keywords SET Ctr = 0, Clicks = 0, Cost = 0, "
              + "Impressions = 0, Conversions = 0, Transactions = 0, "
              + "IsStatsAvailable =  0;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    } 
  }  
  
  public void addStatsToBatch(float ctr, long clicks, float cost, long imp,
      long conv1PerClick, long convManyPerClick, float avgPosition, 
      float adGroupMaxCpa, float costConv1PerClick, float costConvManyPerClick, 
      float firstPageCPC, long keywordId, long adGroupId) {
    try {
      prepStatsUpdate.setDouble(1, ctr);
      prepStatsUpdate.setLong(2, clicks);
      prepStatsUpdate.setDouble(3, cost);
      prepStatsUpdate.setLong(4, imp);
      prepStatsUpdate.setLong(5, conv1PerClick);
      prepStatsUpdate.setLong(6, convManyPerClick);
      prepStatsUpdate.setDouble(7, avgPosition);
      prepStatsUpdate.setDouble(8, adGroupMaxCpa);
      prepStatsUpdate.setDouble(9, costConv1PerClick);
      prepStatsUpdate.setDouble(10, costConvManyPerClick);
      prepStatsUpdate.setDouble(11, firstPageCPC);
      prepStatsUpdate.setLong(12, keywordId);
      prepStatsUpdate.setLong(13, adGroupId);
      prepStatsUpdate.addBatch();    
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  public void executeStatsBatch() {
    try {
      prepStatsUpdate.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  public ResultSet getStats() {
    ResultSet rs = null;
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "SELECT AdGroupId, avg(Ctr) Ctr, SUM(Clicks) Clicks, "
          + "SUM(Cost) Cost, SUM(Impressions) Impressions, "
          + "SUM(Conversions) Conversions, "
          + "SUM(Transactions) Transactions, "
          + "AVG(averagePosition) AveragePosition, "
          + "AVG(adGroupMaxCpa) AdGroupMaxCpa, "
          + "AVG(costPerConverstion) CostPerConverstion, "
          + "AVG(costPerTransaction) CostPerTransaction, "
          + "AVG(firstPageCpc) FirstPageCpc FROM Keywords "
          + "WHERE IsStatsAvailable = 1 GROUP BY AdGroupId;");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return rs;
  }

  /**
   * Reset all campaigns status flags to its default values
   */
  public void resetStatus() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE Keywords SET IsUpdated = 0, IsTouched = 0");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }
}
